﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Configuration;

using System.Data.SqlClient;
using MySql.Data.MySqlClient;

namespace JA.Negocios
{
    public static partial class ModelBO
    {

        public static AreaComumVO getAreaComum(int idAreaComum, string areaComum)
        {
            AreaComumVO areaComumVO = null;
            List<AreaComumVO> listAreaComumVOs = getAreaComums(idAreaComum, areaComum);
            if (listAreaComumVOs != null && listAreaComumVOs.Count > 0)
            {
                areaComumVO = listAreaComumVOs[0];
            }
            return areaComumVO;
        }

        public static List<AreaComumVO> getAreaComums()
        {
            return getAreaComums(null,null);
        }

        public static List<AreaComumVO> getAreaComums(int? idAreaComum, string areaComum)
        {
            string where = "WHERE ";
            StringBuilder query = new StringBuilder();

            query.AppendLine("SELECT IdAreaComum, AreaComum, Cadastro, CadastradoPor, Edicao, EditadoPor ");
            query.AppendLine("FROM areacomum ");

            if (idAreaComum != null)
            {
                query.AppendLine(where + "IdAreaComum=?IdAreaComum ");
                where = "AND ";
            }
            if (areaComum != null)
            {
                query.AppendLine(where + "AreaComum LIKE ?AreaComum ");
                where = "AND ";
            }

            query.AppendLine("ORDER BY AreaComum ");

            MySqlCommand cmd = getConnection();
            cmd.CommandText = query.ToString();

            if (idAreaComum != null) cmd.Parameters.AddWithValue("?IdAreaComum", idAreaComum);
            if (areaComum != null) cmd.Parameters.AddWithValue("?AreaComum", "%" + areaComum + "%");

            MySqlDataReader reader = null;
            try
            {
                cmd.Connection.Open();

                reader = cmd.ExecuteReader();

                List<AreaComumVO> listAreaComum = new List<AreaComumVO>();

                while (reader.Read())
                {
                    AreaComumVO areaComumVO = AreaComumVO.CreateAreaComumVO();
                    areaComumVO.idAreaComum = reader.GetInt32(reader.GetOrdinal("IdAreaComum"));
                    areaComumVO.areaComum = reader.GetString(reader.GetOrdinal("AreaComum"));
                    areaComumVO.cadastro = reader.GetDateTime(reader.GetOrdinal("Cadastro"));
                    areaComumVO.cadastradoPor = reader.GetString(reader.GetOrdinal("CadastradoPor"));
                    areaComumVO.edicao = reader.GetDateTime(reader.GetOrdinal("Edicao"));
                    areaComumVO.editadoPor = reader.GetString(reader.GetOrdinal("EditadoPor"));
                    listAreaComum.Add(areaComumVO);
                }
                reader.Close();
                return listAreaComum;
            }
            catch (Exception ex){ throw ex; }
            finally { cmd.Connection.Close(); }
        }

        public static void setAreaComum(AreaComumVO areaComumVO)
        {
            StringBuilder query = new StringBuilder();

            query.AppendLine("UPDATE areacomum ");
            query.AppendLine("SET AreaComum=?AreaComum, Cadastro=?Cadastro, CadastradoPor=?CadastradoPor, Edicao=?Edicao, EditadoPor=?EditadoPor ");
            query.AppendLine("WHERE IdAreaComum=?IdAreaComum ");

            MySqlCommand cmd = getConnection();
            cmd.CommandText = query.ToString();

            cmd.Parameters.AddWithValue("?IdAreaComum", areaComumVO.idAreaComum);
            cmd.Parameters.AddWithValue("?AreaComum", areaComumVO.areaComum);
            cmd.Parameters.AddWithValue("?Cadastro", areaComumVO.cadastro);
            cmd.Parameters.AddWithValue("?CadastradoPor", areaComumVO.cadastradoPor);
            cmd.Parameters.AddWithValue("?Edicao", areaComumVO.edicao);
            cmd.Parameters.AddWithValue("?EditadoPor", areaComumVO.editadoPor);

            try
            {
                cmd.Connection.Open();

                cmd.ExecuteNonQuery();
            }
            catch (Exception ex) { throw ex; }
            finally { cmd.Connection.Close(); }

        }

        public static long newAreaComum(AreaComumVO areaComumVO)
        {
            StringBuilder query = new StringBuilder();

            query.AppendLine("INSERT INTO areacomum ");
            query.AppendLine("  ( AreaComum, Cadastro, CadastradoPor, Edicao, EditadoPor ) ");
            query.AppendLine("VALUES (?AreaComum, ?Cadastro, ?CadastradoPor, ?Edicao, ?EditadoPor ) ");

            MySqlCommand cmd = getConnection();
            cmd.CommandText = query.ToString();

            cmd.Parameters.AddWithValue("?AreaComum", areaComumVO.areaComum);
            cmd.Parameters.AddWithValue("?Cadastro", areaComumVO.cadastro);
            cmd.Parameters.AddWithValue("?CadastradoPor", areaComumVO.cadastradoPor);
            cmd.Parameters.AddWithValue("?Edicao", areaComumVO.edicao);
            cmd.Parameters.AddWithValue("?EditadoPor", areaComumVO.editadoPor);

            try
            {
                cmd.Connection.Open();
                cmd.ExecuteNonQuery();
                return cmd.LastInsertedId;
            }
            catch (Exception ex) { throw ex; }
            finally { cmd.Connection.Close(); }

        }

        public static void delAreaComum(AreaComumVO areaComumVO)
        {
            StringBuilder query = new StringBuilder();

            query.AppendLine("DELETE FROM areacomum ");
            query.AppendLine("WHERE IdAreaComum=?IdAreaComum ");

            MySqlCommand cmd = getConnection();
            cmd.CommandText = query.ToString();

            cmd.Parameters.AddWithValue("?IdAreaComum", areaComumVO.idAreaComum);

            try
            {
                cmd.Connection.Open();

                cmd.ExecuteNonQuery();
            }
            catch (Exception ex) { throw ex; }
            finally { cmd.Connection.Close(); }

        }
    }
}
